package com.fightingFish.user.dao.impl;

import com.fightingFish.user.bean.Anchor;
import com.fightingFish.user.bean.State;
import com.fightingFish.user.bean.User;
import com.fightingFish.user.dao.AnchorDao;
import com.fightingFish.util.DateUtil;
import com.fightingFish.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2018/12/18.
 */
public class AnchorDaoImpl implements AnchorDao {

    /**
     * 根据关键字查询主播
     *
     * @param keyWords
     * @return
     */
    @Override
    public List<Anchor> queryAnchorByKeyWords(String keyWords) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个集合用于接收查询到的主播
        List<Anchor> anchorList = new ArrayList<Anchor>();
        try {
            //定义sql语句
            String sql = "SELECT * from user u join anchor a on u.anchorOrUser = a.anchorId where u.userId like ? or u.nickName like ? or a.roomId like ? or a.roomName like ?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setString(1, "%" + keyWords + "%");
            ps.setString(2, "%" + keyWords + "%");
            ps.setString(3, "%" + keyWords + "%");
            ps.setString(4, "%" + keyWords + "%");
            //执行sql语句
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建Anchor对象
                Anchor anchor = new Anchor();
                anchor.setUserId(rs.getInt("userId"));//id
                anchor.setPhotoImgUrl(rs.getString("photoImgUrl"));//头像
                anchor.setNickName(rs.getString("nickName"));//昵称
                anchor.setRoomId(rs.getInt("roomId"));//房间号
                anchor.setSignInfo(rs.getString("signInfo"));//个性签名
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间路径名

                //将Anchor对象添加到集合中
                anchorList.add(anchor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return anchorList;
    }

    /**
     * 查询当前登录用户关注的主播列表
     *
     * @param userId
     * @return
     */
    @Override
    public List<Anchor> queryAttentionAnchor(int userId) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个集合存储Anchor对象
        List<Anchor> anchorList = new ArrayList<Anchor>();
        try {
            String sql = "select * from attention LEFT JOIN `user` ON\n" +
                    "       attention.concernId=`user`.userId \n" +
                    "       LEFT JOIN anchor ON\n" +
                    "       `user`.anchorOrUser=anchorId\n" +
                    "       WHERE attention.concernId=?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, userId);
            //执行sql语句
            rs = ps.executeQuery();
            while (rs.next()) {
                Anchor a=new AnchorDaoImpl().selectInfoByAnchorId(rs.getInt("beConcernedId"));
                anchorList.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return anchorList;
    }

    /**
     * 根据主播房间号查询主播信息
     *
     * @param roomId
     * @return
     */
    @Override
    public Anchor queryAnchorByRoomId(int roomId) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个集合存储Anchor对象
        Anchor anchor = null;
        try {
            String sql = "SELECT * from state join (\n" +
                    "select * from user u,anchor a,state s where u.anchorOrUser=a.anchorId and u.state=s.stateId) re\n" +
                    " on state.stateId = re.liveBroadcastType where re.roomId=?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, roomId);
            //执行sql语句
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建Anchor对象
                anchor = new Anchor();
                anchor.setUserId(rs.getInt("userId"));//id
                anchor.setPhotoImgUrl(rs.getString("photoImgUrl"));//头像
                anchor.setNickName(rs.getString("nickName"));//昵称
                anchor.setRoomId(rs.getInt("roomId"));//房间号

                //创建直播类型对象
                State liveBroadcastType = new State();
                liveBroadcastType.setStateName(rs.getString("stateName"));//直播类型
                anchor.setLiveBroadcastType(liveBroadcastType);

                anchor.setSignInfo(rs.getString("signInfo"));//个性签名
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间路径名
                anchor.setBalance(rs.getDouble("balance"));//主播余额
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return anchor;
    }

    /**
     * 根据主播直播类型查询主播列表
     *
     * @param type
     * @return
     */
    @Override
    public List<Anchor> queryAnchorByType(int type) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个集合存储Anchor对象
        List<Anchor> anchorList = new ArrayList<Anchor>();
        try {
            //定义sql语句
            String sql = "SELECT * from anchor a join user u on a.anchorId = u.anchorOrUser where a.liveBroadcastType = ?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, type);
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建Anchor对象
                Anchor anchor = new Anchor();
                anchor.setUserId(rs.getInt("userId"));//id
                anchor.setPhotoImgUrl(rs.getString("photoImgUrl"));//头像
                anchor.setNickName(rs.getString("nickName"));//昵称
                anchor.setRoomId(rs.getInt("roomId"));//房间号
                anchor.setSignInfo(rs.getString("signInfo"));//个性签名
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间路径名
                anchor.setRoomImgUrl(rs.getString("roomImgUrl"));//房间封面图
                anchorList.add(anchor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return anchorList;
    }

    /**
     * 根据主播关注量查询主播列表
     *
     * @return
     */
    @Override
    public List<Anchor> queryAnchorByAttention() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个集合存储Anchor对象
        List<Anchor> anchorList = new ArrayList<Anchor>();
        try {
            //定义sql语句
            String sql = "SELECT * from anchor a join user u on a.anchorId = u.anchorOrUser order by a.attentionNums DESC limit 4";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建Anchor对象
                Anchor anchor = new Anchor();
                anchor.setUserId(rs.getInt("userId"));//id
                anchor.setPhotoImgUrl(rs.getString("photoImgUrl"));//头像
                anchor.setNickName(rs.getString("nickName"));//昵称
                anchor.setRoomId(rs.getInt("roomId"));//房间号
                anchor.setSignInfo(rs.getString("signInfo"));//个性签名
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间路径名
                anchor.setRoomImgUrl(rs.getString("roomImgUrl"));//房间封面图
                anchorList.add(anchor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return anchorList;
    }

    /**
     * 根据主播申请时间查询最新的4位主播列表
     *
     * @return
     */
    @Override
    public List<Anchor> queryAnchorByCreateTime() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        //创建一个集合存储Anchor对象
        List<Anchor> anchorList = new ArrayList<Anchor>();
        try {
            //定义sql语句
            String sql = "SELECT * from anchor a join user u on a.anchorId = u.anchorOrUser order by u.createTime desc limit 4";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                //创建Anchor对象
                Anchor anchor = new Anchor();
                anchor.setUserId(rs.getInt("userId"));//id
                anchor.setPhotoImgUrl(rs.getString("photoImgUrl"));//头像
                anchor.setNickName(rs.getString("nickName"));//昵称
                anchor.setRoomId(rs.getInt("roomId"));//房间号
                anchor.setSignInfo(rs.getString("signInfo"));//个性签名
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间路径名
                anchor.setRoomImgUrl(rs.getString("roomImgUrl"));//房间封面图
                anchorList.add(anchor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return anchorList;
    }

    /**
     * 根据用户ID给用户推荐关注（本身并未关注的粉丝量排行前4的主播）
     *
     * @param userId
     * @return
     */
    @Override
    public List<Anchor> recommendAttention(int userId) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Anchor anchor = null;
        ArrayList<Anchor> list = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            String sql = "SELECT `user`.userId,aaa.* from `user` join (\n" +
                    "SELECT * from anchor an where an.anchorId \n" +
                    "not in \n" +
                    "(SELECT u.anchorOrUser from attention a join `user` u on a.beConcernedId = u.userId where u.anchorOrUser > 0 and concernId <> ? )\n" +
                    ") aaa\n" +
                    "on `user`.anchorOrUser=aaa.anchorId ORDER BY attentionNums desc limit 4";

            ps = connection.prepareStatement(sql);
            ps.setInt(1, userId);
            rs = ps.executeQuery();
            while (rs.next()) {
                anchor = new Anchor();
                anchor.setAnchorId(rs.getInt("anchorId"));//主键Id
                anchor.setRoomId(rs.getInt("roomId"));//房间号
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间地址
                //创建直播类型对象
                State liveBroadcastType = new State();
                liveBroadcastType.setStateName(rs.getString("stateName"));//直播类型
                anchor.setLiveBroadcastType(liveBroadcastType);

                anchor.setAttentionNums(rs.getInt("attentionNums"));//关注量
                anchor.setRoomImgUrl(rs.getString("roomImgUrl"));//直播房间封面路径
                anchor.setUserId(rs.getInt("userId"));//用户id
                list.add(anchor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, ps, rs);
        }
        return list;
    }

    /**
     * 通过主播id查询主播的个人信息
     *
     * @param anchorId
     * @return
     */
    @Override
    public Anchor selectInfoByAnchorId(Integer anchorId) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //定义sql语句
            String sql = " select userId,nickName,photoImgUrl,state,anchorOrUser,createTime,balance,signDay,\n" +
                    "                    sex,signInfo,userlevel,phone,email,\n" +
                    "                    anchorId,roomId,roomUrl,liveBroadcastType,attentionNums,roomImgUrl,\n" +
                    "                    roomName,stateId\n" +
                    "                     from user,anchor,state\n" +
                    "                    where `user`.anchorOrUser=anchor.anchorId and \n" +
                    "                    anchor.liveBroadcastType=state.stateId     and\n" +
                    "                   `user`.userId=?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, anchorId);
            //执行sql语句
            rs = ps.executeQuery();
            if (rs.next()) {
                Anchor a = new Anchor();
                a.setAnchorOrUser(rs.getInt("anchorOrUser"));
                a.setBalance(rs.getDouble("balance"));
                String date = rs.getString("createTime");
                a.setCreateTime(DateUtil.string2Date(date, "yyyyMMddHHssSSS"));
                a.setEmail(rs.getString("email"));
                a.setNickName(rs.getString("nickName"));
                a.setPhone(rs.getString("phone"));
                a.setPhotoImgUrl(rs.getString("photoImgUrl"));
                a.setSex(rs.getString("sex"));
                a.setUserLevel(rs.getInt("userlevel"));
                a.setSignDay(rs.getInt("signDay"));
                a.setSignInfo(rs.getString("signInfo"));
//                a.setState(rs.getInt("state"));
                a.setUserId(rs.getInt("userId"));
                a.setNickName(rs.getString("nickName"));

                a.setAnchorId(rs.getInt("anchorId"));
                a.setAttentionNums(rs.getInt("attentionNums"));
                a.setRoomId(rs.getInt("roomId"));
                a.setRoomName(rs.getString("roomName"));
                a.setRoomUrl(rs.getString("roomUrl"));
                a.setRoomImgUrl(rs.getString("roomImgUrl"));
//                a.setLiveBroadcastType(rs.getInt("stateId"));
                return a;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, rs);
        }
        return null;
    }

    /**
     * 通过id查询到当前主播的余额，及你要提现的金额
     * 实现提现
     *
     * @param userId
     * @param balance
     * @return
     */
    @Override
    public Double CashBalance(Integer userId, Double balance) {
        Connection con = null;
        PreparedStatement ps = null;
        //定义sql语句
        String sql = "update anchor set balance=balance-? where userId=?";
        con = JdbcUtil.getConnection();
        //给占位符赋值
        try {
            ps = con.prepareStatement(sql);
            ps.setDouble(1, balance);
            ps.setInt(2, userId);
            //执行sql语句
            ps.executeUpdate();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        return selectInfoByAnchorId(userId).getBalance();
    }

    /**
     * 通过id查询到当前主播的粉丝量
     *
     * @param anchorId
     * @return
     */
    @Override
    public Integer selectAttentionNumsByid(Integer anchorId) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            //定义sql语句
            String sql = "select attentionNums from anchor where anchorId=?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, anchorId);
            //执行sql语句
            if (ps.executeQuery().next()) {
                return ps.executeQuery().getInt("attentionNums");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, null);
        }
        return null;
    }


    /**
     * 主播可以通过主播id修改个人的信息
     * （密码，头像，昵称，性别，个性签名，手机号，邮箱）
     * （房间名，直播类型，直播间封面）
     */
    @Override
    public boolean updateInfoByAnvhorid(Integer anchorid, String nickName, String sex, String signInfo, String phone, String email) {
        Connection con = null;
        PreparedStatement ps = null;
        //定义sql语句
        String sql = "update user set nickName=?,sex=?,signInfo=?,phone=?,email=? where userId=? ";
        con = JdbcUtil.getConnection();
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, nickName);
            ps.setString(2, sex);
            ps.setString(3, signInfo);
            ps.setString(4, phone);
            ps.setString(5, email);
            ps.setInt(6, anchorid);
            int row = ps.executeUpdate();
            return row > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 通过主播id遍历当前主播间的全部粉丝信息
     */
    @Override
    public ArrayList<User> selectAllByAnchor(Integer anchorid) {
//        System.out.println("selectAllByAnchor");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet re = null;
        ArrayList<User> au = new ArrayList<User>();
        try {
            //定义sql语句
            String sql = "select `user`.userId from attention LEFT JOIN user on \n" +
                    "attention.concernId=user.userId WHERE attention.beConcernedId=?";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            //给占位符赋值
            ps.setInt(1, anchorid);
            //执行sql语句
            re = ps.executeQuery();
            while (re.next()) {
                User u = new UserDaoImpl().selectInfoByid(re.getInt("userId"));
                au.add(u);
            }
            return au;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(con, ps, null);
        }
        return null;
    }
    /**
     * 方法概述：获取全部主播信息
     * @return
     */
    @Override
    public List<Anchor> getAnchorAll() {
        Connection con  = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Anchor> list = new ArrayList<>();
        Anchor anchor = null;
        try {
            String sql = "SELECT state.stateName as stateType,r.* from state join (\n" +
                    "select * from user u,anchor a,state s where u.anchorOrUser=a.anchorId and u.state=s.stateId) r \n" +
                    "on state.stateId=r.liveBroadcastType";
            con = JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                anchor  = new Anchor();
                anchor.setUserId(rs.getInt("userId"));
                anchor.setNickName(rs.getString("nickName"));
                anchor.setPhotoImgUrl(rs.getString("photoImgUrl"));
                anchor.setAnchorOrUser(rs.getInt("anchorOrUser"));
                State state = new State(rs.getString("stateName"));
                anchor.setState(state);
                anchor.setCreateTime((rs.getTime("createTime")));
                anchor.setBalance(rs.getDouble("balance"));
                anchor.setPhone(rs.getString("phone"));
                anchor.setEmail(rs.getString("email"));
                anchor.setAnchorId(rs.getInt("anchorId"));//主播Id
                anchor.setRoomId(rs.getInt("roomId"));//主播房间号
                anchor.setRoomName(rs.getString("roomName"));//房间名
                anchor.setRoomUrl(rs.getString("roomUrl"));//房间地址
                anchor.setLiveBroadcastType(new State(rs.getString("stateType")));//直播类型
                anchor.setAttentionNums(rs.getInt("attentionNums"));

                list.add(anchor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(con,ps,rs);
        }
        return list;
    }

    @Override
    public User getAnchorUserId(int number) {
        Connection con =null;
        PreparedStatement ps =null;
        ResultSet rs = null;
        User user = null;
        try {
            String sql  = "select  * from user u join state s where u.state=s.stateId and userId = ? ";
            con=JdbcUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1,number);
            rs = ps.executeQuery();
            if (rs.next()){
                user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setNickName(rs.getString("nickName"));
                user.setPhotoImgUrl(rs.getString("photoImgUrl"));
                user.setAnchorOrUser(rs.getInt("anchorOrUser"));
                State state = new State(rs.getString("stateName"));
                user.setState(state);
                user.setCreateTime((rs.getTime("createTime")));
                user.setBalance(rs.getDouble("balance"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtil.close(con,ps,rs);
        }
        return user;
    }
}
